# This file is used to create the user_info_ext table in the database.

'''


-- 泡茶表
CREATE TABLE IF NOT EXISTS tea_pots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    md5 VARCHAR(50) NOT NULL UNIQUE,
    device_sn VARCHAR(50) NOT NULL,
    pot_json VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
)

-- 硬件记录表
CREATE TABLE IF NOT EXISTS hardware_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_sn VARCHAR(50) NOT NULL,
    md5 VARCHAR(50) NOT NULL,
    hardware_json VARCHAR(200) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
)

-- 短信发送记录表
CREATE TABLE IF NOT EXISTS sms_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_sn VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(11) NOT NULL,
    template_code VARCHAR(50) NOT NULL,
    content VARCHAR(200) NOT NULL,
    send_status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
)

'''
import logging
import hashlib
import datetime
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class SmsMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_tea_pots(self, device_sn, pot_json):

        md5 = hashlib.md5((pot_json+device_sn).encode('utf-8')).hexdigest()

        sql = "INSERT INTO tea_pots (device_sn, md5, pot_json) VALUES (?,?,?)"
        params = (device_sn, md5, pot_json)
        try:
            self.cursor.execute(sql, params)
            
        except Exception as e:
            logger.error("Insert tea pots failed. Error: {}".format(e))
            return False

        else:

            self.conn.commit()
            logger.info("Insert tea pots success.")

            return True
            


    def insert_hardware_records(self, device_sn, hardware_json):

        md5 = hashlib.md5((hardware_json+device_sn).encode('utf-8')).hexdigest()

        sql = "INSERT INTO hardware_records (device_sn, md5, hardware_json) VALUES (?,?,?)"
        params = (device_sn, md5, hardware_json)
        try:
            self.cursor.execute(sql, params)
            
        except Exception as e:
            logger.error("Insert hardware records failed. Error: {}".format(e))

        else:
            self.conn.commit()
            logger.info("Insert hardware records success.")
            return True



    def insert_sms_records(self, device_sn, name, phone, template_code, content, send_status):

        sql = "INSERT INTO sms_records (device_sn, name, phone, template_code, content, send_status) VALUES (?,?,?,?,?,?)"
        params = (device_sn, name, phone, template_code, content, send_status)
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            logger.info("Insert sms records success.")
        except Exception as e:
            logger.error("Insert sms records failed. Error: {}".format(e))

    def select_send_filter_expires_soon_notification(self, device_sn):
        '''查询最近3天发送过这个设备的过期提醒短信'''

        sql = "SELECT * FROM sms_records WHERE device_sn = ? AND template_code = 'SMS_467500556' AND created_at > datetime('now', '-3 days')"
        
        params = (device_sn,)
        try:
            self.cursor.execute(sql, params)
            
        except Exception as e:
            logger.error("Select send filter expires soon notification failed. Error: {}".format(e))
            return None 
        
        else:
            logger.info("Select send filter expires soon notification success.")
            results = self.cursor.fetchone()
            return results

        
    def select_send_original_water_bucket_empty_notification(self, device_sn):

        '''查询最近半小时发送过这个设备的水桶已满提醒短信'''

        sql = "SELECT * FROM sms_records WHERE device_sn = ? AND template_code = 'SMS_467585524' AND created_at > datetime('now', '-0.5 hours')"


        params = (device_sn,)
        try:
            self.cursor.execute(sql, params)
            
        except Exception as e:
            logger.error("Select send original water bucket empty notification failed. Error: {}".format(e))
            return None 
        
        else:
            logger.info("Select send original water bucket empty notification success.")
            results = self.cursor.fetchone()
            return results

    def select_send_waste_water_bucket_full_notification(self, device_sn):

        '''查询最近半小时发送过这个设备的垃圾桶已满提醒短信'''

        sql = "SELECT * FROM sms_records WHERE device_sn = ? AND template_code = 'SMS_467430469' AND created_at > datetime('now', '-0.5 hours')"


        params = (device_sn,)
        try:
            self.cursor.execute(sql, params)
            
        except Exception as e:
            logger.error("Select send waste water bucket full notification failed. Error: {}".format(e))

            return None 
        
        else:
            logger.info("Select send waste water bucket full notification success.")
            results = self.cursor.fetchone()
            return results

    def select_send_max_tea_times_notification(self, device_sn):
        '''查询最近10分钟发送过这个设备的泡茶次数达到上限提醒短信'''

        sql = "SELECT * FROM sms_records WHERE device_sn = ? AND template_code = 'SMS_467385534' AND created_at > datetime('now', '-10 minutes')"

        params = (device_sn,)

        try:
            self.cursor.execute(sql, params)
            
        except Exception as e:
            logger.error("Select send max tea times notification failed. Error: {}".format(e))
            return None 
        
        else:
            logger.info("Select send max tea times notification success.")
            results = self.cursor.fetchone()
            return results
        

    def select_sms_records(self, device_sn=None, phone=None, template_code=None, send_status=None, start_time=None, end_time=None, limit=20, page=1):
        '''查询短信发送记录'''

        sql = "SELECT * FROM sms_records"
        params = []
        if device_sn or phone or template_code or send_status or start_time or end_time:
            sql += " WHERE "

        if device_sn:
            sql += "device_sn = ?"
            params.append(device_sn)
            if phone or template_code or send_status or start_time or end_time:
                sql += " AND "

        if phone:
            sql += "phone = ?"
            params.append(phone)
            if template_code or send_status or start_time or end_time:
                sql += " AND "

        if template_code:
            sql += "template_code = ?"
            params.append(template_code)
            if send_status or start_time or end_time:
                sql += " AND "

        if send_status:
            sql += "send_status = ?"
            params.append(send_status)
            if start_time or end_time:
                sql += " AND "

        if start_time:
            sql += "created_at > ?"
            params.append(start_time)
            if end_time:
                sql += " AND "

        if end_time:
            sql += "created_at < ?"
            params.append(end_time)

        sql += " ORDER BY created_at DESC"
        sql += " LIMIT ?"
        params.append(int(limit))
        sql += " OFFSET ?"
        params.append((int(page)-1)*int(limit))

        try:
            self.cursor.execute(sql, tuple(params))
            
        except Exception as e:
            logger.error("Select sms records failed. Error: {}".format(e))
            return None 
        
        else:
            logger.info("Select sms records success.")
            results = self.cursor.fetchall()
            for result in results:

                # 将 created_at 字段转换为北京时间 '%Y-%m-%d %H:%M:%S
                utc_time = datetime.datetime.strptime(result["created_at"], '%Y-%m-%d %H:%M:%S')
                beijing_time = utc_time + datetime.timedelta(hours=8)
                result["created_at"] = beijing_time.strftime('%Y-%m-%d %H:%M:%S')
            return results


    def count_sms_records(self, device_sn=None, phone=None, template_code=None, send_status=None, start_time=None, end_time=None):
        '''计算短信发送记录总数'''

        count_sql = "SELECT COUNT(*) FROM sms_records"
        count_params = []
        if device_sn or phone or template_code or send_status or start_time or end_time:
            count_sql += " WHERE "

        if device_sn:
            count_sql += "device_sn = ?"
            count_params.append(device_sn)
            if phone or template_code or send_status or start_time or end_time:
                count_sql += " AND "

        if phone:
            count_sql += "phone = ?"
            count_params.append(phone)
            if template_code or send_status or start_time or end_time:
                count_sql += " AND "

        if template_code:
            count_sql += "template_code = ?"
            count_params.append(template_code)
            if send_status or start_time or end_time:
                count_sql += " AND "

        if send_status:
            count_sql += "send_status = ?"
            count_params.append(send_status)
            if start_time or end_time:
                count_sql += " AND "

        if start_time:
            count_sql += "created_at > ?"
            count_params.append(start_time)
            if end_time:
                count_sql += " AND "

        if end_time:
            count_sql += "created_at < ?"
            count_params.append(end_time)

        try:
            self.cursor.execute(count_sql, tuple(count_params))
            
        except Exception as e:
            logger.error("Count sms records failed. Error: {}".format(e))
            return None
        
        else:
            total_count = self.cursor.fetchone()["COUNT(*)"]

            return total_count


